/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.rie.dao;

import com.rie.model.Report;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import rie.model.CPU;
import rie.model.Memoire;
import rie.model.Processus;
import rie.model.SystemeInfo;

/**
 *
 * @author Michael
 */
public class ReportDAO {

    private Connection connection;

    public ReportDAO() {
        try {
            connection = DriverManager.getConnection("jdbc:derby://localhost:1527/monitoring", "app", "app");
        } catch (SQLException ex) {
            Logger.getLogger(ReportDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void saveReport(SystemeInfo si, boolean saveCpu, boolean saveMemoire, boolean saveProcess) {
        try {
            Statement sta = connection.createStatement();

            int idMemory = 0;
            int idcpu = 0;

            if (saveMemoire) {
                //save memory
                sta.executeUpdate("INSERT INTO MEMORY"
                        + " (freeMemory)"
                        + " VALUES("
                        + si.getMemoire().getFreeRam()
                        + ")", Statement.RETURN_GENERATED_KEYS);

                ResultSet rs = sta.executeQuery("SELECT MAX(ID) FROM MEMORY");
                rs.next();
                idMemory = rs.getInt(1);
            }
            if (saveCpu) {
                //save cpu
                sta.executeUpdate("INSERT INTO CPU"
                        + " (cpu)"
                        + " VALUES("
                        + si.getCpu().getLoadCPU()
                        + ")");
                ResultSet rs2 = sta.executeQuery("SELECT MAX(ID) FROM CPU");
                rs2.next();
                idcpu = rs2.getInt(1);
            }
            //save report
            String insertReport = "INSERT INTO REPORT"
                    + " (IP_SERVEUR,ID_MEMORY,ID_CPU,COMMENT)"
                    + " VALUES('"
                    + si.getServeur()
                    + "', "
                    + idMemory
                    + ", "
                    + idcpu
                    + ", '"
                    + si.getCommentaire()
                    + "')";
            sta.executeUpdate(insertReport);
            if (saveProcess) {
                ResultSet rs3 = sta.executeQuery("SELECT MAX(ID) FROM REPORT");
                rs3.next();
                int idreport = rs3.getInt(1);
                for (Processus p : si.getListProcessus()) {
                    sta.executeUpdate("INSERT INTO PROCESS"
                            + " (ID_REPORT,NAME,PID,MEMORYUSAGE)"
                            + " VALUES("
                            + idreport
                            + ", '"
                            + p.getName()
                            + "', '"
                            + p.getPid()
                            + "', '"
                            + p.getMemoryUsage()
                            + "')", Statement.RETURN_GENERATED_KEYS);
                }
            }
            sta.close();
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(ReportDAO.class.getName()).log(Level.SEVERE, null, ex);
        }


    }

    public List<String> getListExistingServer() {
        List<String> result = new ArrayList<String>();
        try {
            Statement sta = connection.createStatement();
            ResultSet rs3 = sta.executeQuery("SELECT DISTINCT IP_SERVEUR FROM REPORT");
            while (rs3.next()) {
                result.add(rs3.getString(1));
            }
            sta.close();

        } catch (SQLException ex) {
            Logger.getLogger(ReportDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public List<Report> getListReport(String server) {
        List<Report> result = new ArrayList<Report>();
        try {
            Statement sta = connection.createStatement();
            ResultSet rs = null;
            if (server != null && !server.isEmpty() && !server.equals("*")) {
                rs = sta.executeQuery("SELECT * FROM REPORT WHERE IP_SERVEUR LIKE '%" + server + "%'");
            } else {
                rs = sta.executeQuery("SELECT * FROM REPORT");
            }
            while (rs.next()) {
                result.add(new Report(rs.getInt("id"), rs.getInt("id_cpu"), rs.getInt("id_memory"), rs.getInt("process"), rs.getString("ip_serveur"), rs.getString("date")));

            }
            sta.close();

        } catch (SQLException ex) {
            Logger.getLogger(ReportDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public SystemeInfo getReport(int id) {
        SystemeInfo si = null;
        try {
            Statement sta = connection.createStatement();
            ResultSet rs = sta.executeQuery("SELECT * FROM REPORT LEFT JOIN MEMORY ON REPORT.ID_MEMORY=MEMORY.ID LEFT JOIN CPU ON REPORT.ID_CPU=CPU.ID WHERE REPORT.ID=" + id + "");

            if (rs.next()) {
                List<Processus> lp = new ArrayList<Processus>();
                si = new SystemeInfo(new CPU(rs.getInt("cpu")), new Memoire(rs.getInt("freememory")), lp);
                si.setCommentaire(rs.getString("comment"));
                si.setServeur(rs.getString("ip_serveur"));
                ResultSet rs2 = sta.executeQuery("SELECT * FROM PROCESS WHERE ID_REPORT=" + rs.getInt("id"));
                while (rs2.next()) {
                    lp.add(new Processus(rs2.getString("name"), rs2.getString("pid"), rs2.getString("memoryusage")));
                }
                si.setListProcessus(lp);
            }
            sta.close();

        } catch (SQLException ex) {
            Logger.getLogger(ReportDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return si;
    }

    public void deleteReportServer(String ipServer) {
        try {
            Statement sta = connection.createStatement();
            sta.executeUpdate("DELETE FROM REPORT WHERE IP_SERVEUR ='" + ipServer + "'");
        } catch (SQLException ex) {
            Logger.getLogger(ReportDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
